/*
 * returns only changed line item details except the current line item.
 */

ALTER proc sp_load_only_change_order_lineitems
@Sales_Order_ID int
as

begin
	DECLARE @results Table (LineItem_ID int, CCO_Line_Item_No int, 
		Product_ID int, CCO_Qty int, CCO_Taxable int, CCO_Unit_Cost float, 
		CCO_Amount float, CCO_Requested_Date datetime, CCO_Change_Number int, ChangeNo int, CCO_Booking_Date datetime,CCO_Schedule_Date datetime, CCO_PO_Line_Item_No int, Customer_PartNumber varchar(50),Label_Number varchar(50),
		CCO_CUSTOM_PRICE_FIELD1 float, CCO_CUSTOM_TEXT_FIELD1 varchar(1000))
	
	DECLARE @lineitem_id int
	DECLARE @change_no int
	DECLARE @cco_line_item_no int
	DECLARE @product_id int
	DECLARE @cco_qty int
	DECLARE @cco_taxable int
	DECLARE @cco_unit_cost float
	DECLARE @cco_amount float
	DECLARE @cco_requested_date datetime
	DECLARE @cco_change_number int
	DECLARE @cco_booking_date datetime
	DECLARE @cco_schedule_date datetime
	DECLARE @cco_po_line_item_no int
	DECLARE @customer_partnumber varchar(50)
	DECLARE @customer_id int
	DECLARE @label_number varchar(50)
	DECLARE @odm_id int 
	DECLARE @CCO_CUSTOM_PRICE_FIELD1 float
	DECLARE @CCO_CUSTOM_TEXT_FIELD1 varchar(1000)

	SELECT @customer_id = Customer_Id, @odm_id = SO_Odm_Id from Sales_Order where SalesOrder_Id = @sales_order_id
	DECLARE  change_order_lineitems_cursor CURSOR for 
	select LineItem_ID, CCO_Line_Item_No, Product_ID, CCO_Qty, CCO_Taxable, CCO_Unit_Cost, CCO_Amount, CCO_Requested_Date, CCO_Change_Number, ChangeNo, CCO_Booking_Date, CCO_Schedule_Date, CCO_PO_Line_Item_No, CCO_CUSTOMER_PARTNUMBER, CCO_CUSTOM_PRICE_FIELD1, CCO_CUSTOM_TEXT_FIELD1  from Customer_Change_Order_LineItem where SalesOrder_ID = @Sales_Order_ID
	
	open change_order_lineitems_cursor
	fetch next from change_order_lineitems_cursor
	into @lineitem_id, @cco_line_item_no, @product_id, @cco_qty, @cco_taxable, @cco_unit_cost, @cco_amount, @cco_requested_date, @cco_change_number, @change_no, @cco_booking_date, @cco_schedule_date, @cco_po_line_item_no, @customer_partnumber, @CCO_CUSTOM_PRICE_FIELD1, @CCO_CUSTOM_TEXT_FIELD1
        
	while @@fetch_status = 0
	begin
		DECLARE @max_ch_no int
		select @max_ch_no = max(ChangeNo) from Customer_Change_Order_LineItem where SalesOrder_ID = @Sales_Order_ID and LineItem_ID = @lineitem_id
		
		if(@max_ch_no > 0)
		begin
			if(@change_no <> @max_ch_no)
			begin
				insert into @results(LineItem_ID, CCO_Line_Item_No, Product_ID, CCO_Qty, CCO_Taxable, CCO_Unit_Cost, CCO_Amount, CCO_Requested_Date, CCO_Change_Number, ChangeNo, CCO_Booking_Date, CCO_Schedule_Date, CCO_PO_Line_Item_No,Customer_PartNumber,Label_Number, CCO_CUSTOM_PRICE_FIELD1, CCO_CUSTOM_TEXT_FIELD1) values (@lineitem_id, @cco_line_item_no, @product_id, @cco_qty, @cco_taxable, @cco_unit_cost, @cco_amount, @cco_requested_date, @cco_change_number, @change_no, @cco_booking_date, @cco_schedule_date, @cco_po_line_item_no,@customer_partnumber,@label_number, @CCO_CUSTOM_PRICE_FIELD1, @CCO_CUSTOM_TEXT_FIELD1)
			end
		end
		fetch next from change_order_lineitems_cursor
		into @lineitem_id, @cco_line_item_no, @product_id, @cco_qty, @cco_taxable, @cco_unit_cost, @cco_amount, @cco_requested_date, @cco_change_number, @change_no, @cco_booking_date, @cco_schedule_date, @cco_po_line_item_no, @customer_partnumber, @CCO_CUSTOM_PRICE_FIELD1, @CCO_CUSTOM_TEXT_FIELD1
	end

	close change_order_lineitems_cursor
	deallocate change_order_lineitems_cursor

	select * from @results order by LineItem_ID, ChangeNo
end